import os
import openpyxl
from utils.global_log import logger
def creatwb(wbname): 
    '''
    新建excel
    '''  
    if not os.path.exists(wbname):

        wb=openpyxl.Workbook()
        wb.save(filename=wbname)
        logger.info (f"新建{wbname}成功")
    else:
        logger.info (f"{wbname}存在")

def _get_sheet(workbook,sheetname):
    if sheetname in  workbook.sheetnames:
        sheet=workbook[sheetname]
    else:
        sheet=workbook.active
        if (sheetname!=""):
            sheet.title=sheetname
    return sheet

    
def _writews_dictlist(sheet,dictlist):
    baserows=sheet.max_row
    basecols=1
    if baserows<=1:
        # 无表头时以key作为表头
        baserows=1
        for key in dictlist[0].keys():
            _=sheet.cell(row=baserows,column=basecols,value=str(key))
            basecols+=1
        basecols=1
        baserows+=1
    
    for dictdata in dictlist:   
        for val in dictdata.values():
            _=sheet.cell(row=baserows,column=basecols,value=str(val))
            basecols+=1
        basecols=1
        baserows+=1

def writewb_dict(wbname,sheetname,dictlist):
    '''
    写入excel文件 
    @param dictlist 数据体，示例：[{'B':b,'C':c}]
    ''' 
    creatwb(wbname)
    wb=openpyxl.load_workbook(filename=wbname)
    
    sheet =_get_sheet(wb,sheetname)
    _writews_dictlist(sheet,dictlist)
        
    wb.save(filename=wbname)
    wb.close()
    logger.info("保存成功")


def writewb_array(wbname,sheetname,datalist):  
    '''
    写入excel文件 
    @param wbname   即文件名称,
    @param sheetname  工作表名称,可以为空,若为空默认第一个工作表
    @param dataList 数据体，示例：[["A","B"],[1,2],[11,22],[111,222]]
    ''' 
    creatwb(wbname)

    wb=openpyxl.load_workbook(filename=wbname)

    sheet =_get_sheet(wb,sheetname)
    baserows=sheet.max_row
    for row in range(len(datalist)):  # 写入数据
        for col in range(0,len(datalist[row])):
            print(f"{row}:{col}")
            _=sheet.cell(row=row+baserows,column=col+1,value=str(datalist[row][col]))

    wb.save(filename=wbname)
    wb.close()
    logger.info("保存成功")

def readwb(wbname,sheetname):
    '''
    @param wbname   即文件名称,
    @param sheetname  工作表名称,可以为空,若为空默认第一个工作表
    '''
    wb=openpyxl.load_workbook(filename=wbname,read_only=True)
    
    sheet =_get_sheet(wb,sheetname)

    datalist=[]
    for row in sheet.rows:
        data=[]
        for cell in row:
            data.append(cell.value)
        datalist.append(data)

    logger.info (f"{wbname}-{sheetname}：成功读取完成")
    return datalist


if __name__=='__main__':

    excel_file="./excel.xlsx"
    excel_sheet_name="demo"
    writewb_array(excel_file,excel_sheet_name,[["A","B"],[1,2],[11,22],[111,222]]) 
    writewb_dict(excel_file,excel_sheet_name,[{"A":1,"B":2},{"A":11,"B":22}]) 
    dataList=readwb(excel_file,excel_sheet_name)
    logger.info(dataList)

